﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// DB_employee 的摘要描述
/// </summary>
public static class DB_employee
{

    /// <summary>
    /// 確認帳密是否存在(若存在回傳true,若不存在回傳false)
    /// </summary>
    /// <param name="EmployeeId"></param>
    /// <param name="Password"></param>
    /// <returns></returns>
    public static Boolean CheckLogin(string EmployeeId, string Password)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelEmployee where EmployeeId=@EmployeeId and Password=@Password", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        da.SelectCommand.Parameters.AddWithValue("@Password", Password);
        da.Fill(dt);
        if (dt != null)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    /// <summary>
    /// 取得單一行程報名員工名單
    /// </summary>
    /// <returns></returns>
    public static DataTable SelectEmployeeList(string TravelId)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelEmployee where TravelId=@TravelId", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@TravelId", TravelId);
        da.Fill(dt);
        return dt;
    }

    /// <summary>
    /// 取得單一員工資料
    /// </summary>
    /// <returns></returns>
    public static DataTable SelectEmployee(string EmployeeId)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelEmployee where EmployeeId=@EmployeeId", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        da.Fill(dt);
        return dt;
    }


    /// <summary>
    /// 確認報名之員工身分證字號是否重複(true:重複;false:未重複)
    /// </summary>
    /// <returns></returns>
    public static Boolean ChenkIdExist(string EmployeeId)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelEmployee where EmployeeId=@EmployeeId", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        da.Fill(dt);
        if (dt != null) {
            return true;
        } else {
            return false;
        }      
    }

    /// <summary>
    /// 新增員工報名資料
    /// </summary>
    /// <returns></returns>
    public static void InsterTable(string EmployeeId,string Name,string Birthday, string Tel,string Phone, string Email, string Dep, string Food, string Memo, string Password, string TravelId)
    {
        SqlConnection cn = new SqlConnection(Common.DBConnectionString);

        SqlCommand cmd = new SqlCommand(
             "INSERT INTO TravelEmployee (EmployeeId, Name,Birthday,Tel,Phone,Email,Dep,Food,Memo,Password,UpdateTime,TravelId) VALUES (@EmployeeId, @Name,@Birthday,@Tel,@Phone,@Email,@Dep,@Food,@Memo,@Password,@UpdateTime,@TravelId)",
             cn);
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Birthday", Birthday);
        cmd.Parameters.AddWithValue("@Tel", Tel);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@Dep", Dep);
        cmd.Parameters.AddWithValue("@Food", Food);
        cmd.Parameters.AddWithValue("@Memo", Memo);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now.ToString());
        cmd.Parameters.AddWithValue("@TravelId", TravelId);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }

    /// <summary>
    /// 更新員工報名資料
    /// </summary>
    /// <returns></returns>
    public static void UpdateTable(string EmployeeId, string Name, string Birthday, string Tel, string Phone, string Email, string Dep, string Food, string Memo, string Password, string TravelId)
    {

        SqlConnection cn = new SqlConnection(Common.DBConnectionString);

        SqlCommand cmd = new SqlCommand(
    "UPDATE [TravelEmployee] SET [Name] = @Name, [Birthday] = @Birthday, [Tel] = @Tel, [Phone] = @Phone, [Email] = @Email, [Dep] = @Dep, [Food] = @Food, [Memo] = @Memo, [Password] = @Password, [UpdateTime] = @UpdateTime, [TravelId] = @TravelId WHERE [EmployeeId] = @EmployeeId",
     cn);

        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@Birthday", Birthday);
        cmd.Parameters.AddWithValue("@Tel", Tel);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@Dep", Dep);
        cmd.Parameters.AddWithValue("@Food", Food);
        cmd.Parameters.AddWithValue("@Memo", Memo);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now.ToString());
        cmd.Parameters.AddWithValue("@TravelId", TravelId);
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }

    /// <summary>
    /// 刪除員工報名資料(應同步刪除相關的親屬資料)
    /// </summary>
    /// <returns></returns>
    public static void DeleteTable(string EmployeeId)
    {
        SqlConnection cn = new SqlConnection(Common.DBConnectionString);
        SqlCommand cmd = new SqlCommand(
             "DELETE TravelEmployee where employee_id = @employee_id",
             cn);
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}